﻿/*
Commerce by Onex – eCommerce software platform (www.ecommerce.byonex.com)
Copyright (C) 2008-2010 by Onex Group

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using ShopModel;

namespace ShopDAO
{
    public class PollDAO
    {
        private string connectionString;

        public PollDAO()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["csShop"].ConnectionString;
        }

        private SqlConnection Connection()
        {
            return new SqlConnection(connectionString);
        }

        public bool DeletePoll(int PollID)
        {
            SqlConnection sqlC = Connection();
            bool result = false;

            SqlCommand command = new SqlCommand("DeletePoll", sqlC);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@PollID", PollID));

            try
            {
                sqlC.Open();
                result = command.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }

            return result;
        }

        public bool DeletePollAnswer(int PollAnswerID)
        {
            SqlConnection sqlC = Connection();
            bool result = false;

            SqlCommand command = new SqlCommand("DeletePollAnswer", sqlC);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@PollAnswerID", PollAnswerID));

            try
            {
                sqlC.Open();
                result = command.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }

            return result;
        }

        public int? SavePoll(Poll PollToSave, int Language)
        {
            int? result = null;
            SqlConnection sqlC = Connection();
            SqlDataReader sqlR;

            SqlCommand command = new SqlCommand("SavePoll", sqlC);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@PollID", PollToSave.IDPoll));
            command.Parameters.Add(new SqlParameter("@Name", PollToSave.Name));
            command.Parameters.Add(new SqlParameter("@Question", PollToSave.Question));
            command.Parameters.Add(new SqlParameter("@CategoryID", PollToSave.CategoryID));
            command.Parameters.Add(new SqlParameter("@IsActive", PollToSave.IsActive));

            command.Parameters.Add(new SqlParameter("@LanguageID", Language));

            try
            {
                sqlC.Open();

                sqlR = command.ExecuteReader();

                if (sqlR.Read())
                    result = (int?)sqlR[0];
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally { sqlC.Close(); }

            return result;
        }

        public bool SavePollAnswer(PollAnswer Answer, int LanguageID)
        {
            bool result = false;
            SqlConnection sqlC = Connection();

            SqlCommand command = new SqlCommand("SavePollAnswer", sqlC);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@PollAnswerID", Answer.IDPollAnswer));
            command.Parameters.Add(new SqlParameter("@PollID", Answer.PollID));
            command.Parameters.Add(new SqlParameter("@Answer", Answer.Answer));
            command.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

            try
            {
                sqlC.Open();
                result = command.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
            }

            return result;
        }

        public bool IncreaseVote(int PollAnswerID)
        {
            bool result = false;
            SqlConnection sqlC = Connection();
            SqlCommand command = new SqlCommand("IncreaseVote", sqlC);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@PollAnswerID", PollAnswerID));

            try
            {
                sqlC.Open();
                result = command.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
            }
            return result;
        }

        public Poll GetPoll(int? PollID, int? CategoryID, int LangugeID)
        {
            Poll result = null;
            SqlConnection sqlC = Connection();
            SqlDataAdapter sqlA = new SqlDataAdapter();
            DataSet dataSet = new DataSet();

            SqlCommand command = new SqlCommand("GetPoll", sqlC);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@PollID", PollID));
            command.Parameters.Add(new SqlParameter("@CategoryID", CategoryID));
            command.Parameters.Add(new SqlParameter("@LanguageID", LangugeID));

            sqlA.SelectCommand = command;

            try
            {
                sqlC.Open();

                sqlA.Fill(dataSet);

                if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow row = dataSet.Tables[0].Rows[0];
                    result = new Poll((int)row["IDPoll"], (string)row["Name"], (string)row["Question"], (DateTime)row["DateCreated"], (bool)row["IsActive"], (int)row["CategoryID"]);

                    if (dataSet.Tables[1] != null)
                    {
                        foreach (DataRow dr in dataSet.Tables[1].Rows)
                            result.Answers.Add(new PollAnswer((int)dr["IDPollAnswer"], (int)dr["PollID"], (string)dr["Answer"], (int)dr["Votes"]));
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
            }

            return result;
        }

        public List<Poll> GetPolls(int? LangugeID)
        {
            List<Poll> result = new List<Poll>();

            SqlConnection sqlC = Connection();
            SqlDataReader sqlR;

            SqlCommand command = new SqlCommand("GetPolls", sqlC);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@LanguageID", LangugeID));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                while (sqlR.Read())
                    result.Add(new Poll((int)sqlR["IDPoll"], sqlR["Name"].ToString(), sqlR["Question"].ToString(), (DateTime)sqlR["DateCreated"], (bool)(sqlR["IsActive"]), (int)sqlR["CategoryID"]));

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
            }

            return result;
        }

        public List<PollAnswer> GetPollAnswers(int PollID, int LanguageID)
        {
            List<PollAnswer> result = new List<PollAnswer>();

            SqlConnection sqlC = Connection();
            SqlDataReader sqlR;

            SqlCommand command = new SqlCommand("GetPollAnswers", sqlC);
            command.Parameters.Add(new SqlParameter("@PollID", PollID));
            command.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));
            command.CommandType = CommandType.StoredProcedure;

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                while (sqlR.Read())
                    result.Add(new PollAnswer((int)sqlR["IDPollAnswer"], (int)sqlR["PollID"], (string)sqlR["Answer"], (int)sqlR["Votes"]));

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
            }

            return result;
        }
    }
}
